package com.androturka;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class EarthQuakeDB extends SQLiteOpenHelper{

    public static final String DATABASE_NAME 				= "earthquake.sqlite";
    public static final String TABLE_FILTERS				= "filters";
    public static final String TABLE_PARAMETERS 			= "parameters";

    public static final String PARAM_WORKATBACKGROUND 		= "workAtBackGround";
    public static final String PARAM_BARNOTIFICATION 			= "barNotification";
    public static final String PARAM_RINGNOTIFICATION 		= "ringNotification";
    public static final String PARAM_VIBRATIONNOTIFICATION 	= "vibrationNotification";
    public static final String PARAM_LATESTEARTHQUAKETIME 	= "latestEarthQuakeTime";
    
    public static final String FILTER_ID 					= "filterid";
    public static final String FILTER_MAGNITUDE 			= "magnitude";
    public static final String FILTER_DISTANCE 			= "distance";
    public static final String FILTER_CITYID 				= "city";
    
    public static final String CREATE_TABLE_FILTER 			= "CREATE TABLE IF NOT EXISTS "+ 
    														   TABLE_FILTERS + " ('"+ FILTER_ID +"' INTEGER PRIMARY KEY NOT NULL, '"+ 
    		                                                   FILTER_MAGNITUDE +"' FLOAT, '"+ 
    		                                                   FILTER_DISTANCE +"' INTEGER, '"+ 
    														   FILTER_CITYID +"' INTEGER)";
    
    public static final String CREATE_TABLE_PARAMETERS	 	= "CREATE TABLE IF NOT EXISTS "+ TABLE_PARAMETERS + " ('"+ 
    														   PARAM_WORKATBACKGROUND +"' BOOL NOT NULL DEFAULT (1)  ,'"+ 
    														   PARAM_BARNOTIFICATION +"' BOOL NOT NULL  DEFAULT 1, '"+ 
    														   PARAM_RINGNOTIFICATION +"' BOOL NOT NULL  DEFAULT 0, '" + 
    														   PARAM_VIBRATIONNOTIFICATION + "' BOOL NOT NULL  DEFAULT 0, '" + 
    														   PARAM_LATESTEARTHQUAKETIME + "' STRING NOT NULL  DEFAULT 0)";

	public static final String DROP_TABLE_FILTER 			= "DROP TABLE IF EXISTS "+ TABLE_FILTERS;
	public static final String DROP_TABLE_PARAMETERS 		= "DROP TABLE IF EXISTS "+ TABLE_PARAMETERS;

    public static final int DATABASE_VERSION = 1;

    
    SQLiteDatabase dbObject;
	public EarthQuakeDB(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}
	
	public EarthQuakeDB(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		boolean tableParametersExists = false;
		boolean tableFiltersExists = false;
        this.dbObject = db;

		/*
		db.execSQL(DROP_TABLE_FILTER);
        db.execSQL(DROP_TABLE_PARAMETERS);
		*/

		tableParametersExists = isTableExists(TABLE_PARAMETERS); 
		tableFiltersExists = isTableExists(TABLE_FILTERS); 
			
		db.execSQL(CREATE_TABLE_FILTER);
        db.execSQL(CREATE_TABLE_PARAMETERS);

        if ( !tableParametersExists ){
    		insertParameters( 10, 1, 1, 1, "20120210120000");
        }
        	
        if ( !tableFiltersExists ){
            insertFilter(5.0,100,0);
            //insertFilter(4.0, 1000);
        }
        	
	}
	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	}

	
	public boolean getVibrationNotification() {
		return getBooleanParameter( PARAM_VIBRATIONNOTIFICATION ); 
	}

	public boolean getRingNotification() {
		return getBooleanParameter( PARAM_RINGNOTIFICATION ); 
	}

	public boolean getBarNotification() {
		return getBooleanParameter( PARAM_BARNOTIFICATION ); 
	}

	public boolean getWorkAtBackGround() {
		return getBooleanParameter( PARAM_WORKATBACKGROUND ); 
	}

	public String getLatestEarthQuakeTime() {
		
		String valueStr;
		valueStr = getStringParameter( PARAM_LATESTEARTHQUAKETIME );
		if ( valueStr != null )
			return valueStr;
		else
			return "000000000000";
	}

	public ArrayList<Filter> getFilters() {
		ArrayList<Filter> filters = new ArrayList<Filter>();
		int id;
		float magnitude;
		int distance;
		int city;
		
		Cursor cursor = dbObject.query(TABLE_FILTERS, new String[] {FILTER_ID,FILTER_MAGNITUDE,FILTER_DISTANCE, FILTER_CITYID}, null, null, null, null, null);
		
		while ( ( cursor != null ) && cursor.moveToNext() )
		{
			id = cursor.getInt(cursor.getColumnIndex(FILTER_ID));
			magnitude = cursor.getFloat(cursor.getColumnIndex(FILTER_MAGNITUDE));
			distance = cursor.getInt(cursor.getColumnIndex(FILTER_DISTANCE));
			city = cursor.getInt(cursor.getColumnIndex(FILTER_CITYID));
			Filter filter = new Filter(id, magnitude, distance, city);
			filters.add( filter );
			Log.d("DB", "id : " + filter.filterid + " magnitude : "+ filter.magnitude + " distance : " + filter.distance + " city : " + filter.cityid);
		}

		cursor.close();
		
		return filters;
	}

	public void updateVibrationNotification( boolean vibrationNotification ) {
		updateBooleanParameter( PARAM_VIBRATIONNOTIFICATION, vibrationNotification);
	}

	public void updateRingNotificaiton( boolean ringNotification ) {
		updateBooleanParameter( PARAM_RINGNOTIFICATION, ringNotification);
	}

	public void updateBarNotification( boolean barNotification) {
		updateBooleanParameter( PARAM_BARNOTIFICATION, barNotification);
	}

	public void updateWorkAtBackGround( boolean workAtWorkGround) {
		updateBooleanParameter( PARAM_WORKATBACKGROUND, workAtWorkGround);
	}

	public void updateLatestEarthQuakeTime( String latestEarthQuakeTime) {
		updateStringParameter( PARAM_LATESTEARTHQUAKETIME, latestEarthQuakeTime);
	}

	private boolean getBooleanParameter( String parameter){
		
		Cursor cursor = dbObject.query(TABLE_PARAMETERS, new String[] {parameter}, null, null, null, null, null);
		
		if ( cursor.moveToFirst() )
		{
			boolean value =cursor.getString(0).contains("1");
			Log.d("DB", parameter + " " + value );
			cursor.close();
			return value;
		}
		else
		{	
			cursor.close();
			return false;
		}	
	}

	@SuppressWarnings("unused")
	private int getIntegerParameter(String parameter) {
		Cursor cursor = dbObject.query(TABLE_PARAMETERS, new String[] {parameter}, null, null, null, null, null);
		
		if ( cursor.moveToFirst() )
		{
			int value;
			value = cursor.getInt(0);
			Log.d("DB", parameter + " " + value );
			cursor.close();
			return value;
		}
		else
		{	
			cursor.close();
			return 0;			
		}	
	}

	private String getStringParameter( String parameter) {
		Cursor cursor = dbObject.query(TABLE_PARAMETERS, new String[] {parameter}, null, null, null, null, null);
		
		if ( cursor.moveToFirst() )
		{
			String value;
			value = cursor.getString(0);
			Log.d("DB", parameter + " " + value );
			cursor.close();
			return value;
		}
		else	
		{	
			cursor.close();
			return null;			
		}	
	}

	private void updateBooleanParameter( String parameter, boolean value) {
	    ContentValues args = new ContentValues();
	    args.put(parameter, value);
	    dbObject.update(TABLE_PARAMETERS, args, null, null);
	}

	@SuppressWarnings("unused")
	private void updateIntegerParameter( String parameter, int value) {
	    ContentValues args = new ContentValues();
	    args.put(parameter, value);
	    dbObject.update(TABLE_PARAMETERS, args, null, null);		
	}

	private void updateStringParameter( String parameter, String value) {
	    ContentValues args = new ContentValues();
	    args.put(parameter, value);
	    dbObject.update(TABLE_PARAMETERS, args, null, null);
	}
	
	public void insertFilter( double magnitude, int distance, int cityid )
	{
		String sqlString = "INSERT INTO " + TABLE_FILTERS + " VALUES ( null, '" + magnitude + "', '" + distance + "' , '" + cityid + "' )"; 
		Log.d("DB",sqlString);
		dbObject.execSQL(sqlString);
	}

	public void updateFilter( Filter filter )
	{
		String sqlString = "UPDATE " + TABLE_FILTERS + " SET " + FILTER_MAGNITUDE + " = " + filter.magnitude + " , " + FILTER_DISTANCE + " = " + filter.distance + " WHERE " + FILTER_ID + " = " + filter.filterid; 
		Log.d("DB",sqlString);
		dbObject.execSQL(sqlString); 
	}

	public void deleteFilter(int filterid )
	{
		String sqlString = "DELETE FROM " + TABLE_FILTERS + " WHERE " + FILTER_ID + " = " + filterid; 
		Log.d("DB",sqlString);
		dbObject.execSQL(sqlString); 
	}

	public void deleteAllFilter()
	{
		String sqlString = "DELETE FROM " + TABLE_FILTERS; 
		Log.d("DB",sqlString);
		dbObject.execSQL(sqlString); 
	}

	public void deleteParameters()
	{
		String sqlString = "DELETE FROM " + TABLE_PARAMETERS; 
		Log.d("DB",sqlString);
		dbObject.execSQL(sqlString); 
	}
	
	public void insertParameters( int workingFrequency, int barNotification, int ringNotification, int vibrationNotification, String latestEarthQuakeTime )
	{
		String sqlString = "INSERT INTO " + TABLE_PARAMETERS + " VALUES ( '" + workingFrequency + "', '" + barNotification + "', '" + ringNotification + "', '" + vibrationNotification + "', '" + latestEarthQuakeTime + "' )"; 
		Log.d("DB",sqlString);
		dbObject.execSQL(sqlString); 		
	}
	
	boolean isTableExists( String tableName )
	{
	    if (tableName == null || dbObject == null || !dbObject.isOpen())
	    {
	        return false;
	    }
	    Cursor cursor = dbObject.rawQuery("SELECT COUNT(*) FROM sqlite_master WHERE type = ? AND name = ?", new String[] {"table", tableName});
	    if (!cursor.moveToFirst())
	    {
		    cursor.close();
	        return false;
	    }
	    int count = cursor.getInt(0);
	    cursor.close();
	    return count > 0;
	}

	@Override
	public synchronized void close() {
		super.close();
		
		if ( dbObject != null )
			dbObject.close();
	}

}
